/*
 *
 * SimplePCR1000 (Qt4) Version 1.x
 * Copyright (C) 2011  Tyler H. Jones (me@tylerjones.me)
 * http://www.thebasementserver.com/
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 */


#include "database.h"

Database::Database(QObject *parent) :
    QObject(parent)
{
}

bool Database::Connect()
{
    if(db.isOpen()) { return true; }

#ifdef Q_WS_X11
    QDir dir(QDir::homePath() + "/"APP_DIR);
    if(!dir.exists(QDir::homePath() + "/"APP_DIR)) {
        if(!dir.mkpath(QDir::homePath() + "/"APP_DIR)) {
            qDebug() << "FATAL: Cannot create application directory '" << APP_DIR << "' in the current home folder!";
            return false;
        }
        qDebug() << "LOG: Successfully created the application directory in the current home folder!";
    }

    if (!QFile::exists(QDir::homePath() + "/"APP_DIR"/"APP_DBFILE)) {
        QString filename = QString(QDir::homePath() + "/"APP_DIR"/"APP_DBFILE);
        QFile file(filename);
        if (file.open(QIODevice::WriteOnly | QIODevice::Text)) {
            QTextStream stream(&file);
            stream << "" << endl;
            file.close();
            qDebug() << "LOG: The SQLite database file was successfully created!";
        } else {
            file.close();
            qDebug() << "ERROR: Could not create the SQLite database file '" << filename << "'! That's no good...";
            return false;
        }
    }

    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setHostName("localhost");
    QString path = QDir::homePath() + "/"APP_DIR"/"APP_DBFILE;
    db.setDatabaseName(path);
    if(!db.open()) {
        qDebug() << "ERROR: " << db.lastError();
        qFatal("FATAL: Failed to connect to SQLite DB!");
        return false;
    }
    qDebug() << "LOG: Successfully connected to the SQLite DB!";
#endif

#ifdef Q_WS_WIN
    QString DBROOT = QString(QDir::homePath() + "\\" + APP_DIR);
    QString DBPATH = QString(QDir::homePath() + "\\" + APP_DIR + "\\" + APP_DBFILE);
    QDir dir(DBROOT);
    if(!dir.exists(DBROOT)) {
        if(!dir.mkpath(DBROOT)) {
            qDebug() << "FATAL: Cannot create application directory '" << APP_DIR << "' in the current user's Documents and Settings folder!";
            return false;
        }
        qDebug() << "LOG: Successfully created the application directory in the current user's Documents and Settings folder!";
    }
/*
    if (!QFile::exists(DBPATH)) {
        QFile file(DBPATH);
        if (file.open(QIODevice::WriteOnly | QIODevice::Text)) {
            QTextStream stream(&file);
            stream << "" << endl;
            file.close();
            qDebug() << "The SQLite database file was successfully created!";
        } else {
            file.close();
            qDebug() << "Could not create the SQLite database file '" << DBPATH << "'! That's no good...";
            return false;
        }
    }
*/
    db = QSqlDatabase::addDatabase("QSQLITE");
    //db.setHostName("localhost");
    db.setDatabaseName(DBPATH);
    if(!db.open()) {
        qDebug() << "ERROR: " << db.lastError();
        qFatal("FATAL: Failed to connect to SQLite DB!");
        return false;
    } else { qDebug() << "LOG: Successfully connected to the SQLite DB!"; }
#endif

    //Check that there is an Scanners table in the DB
    //db.open();
    QSqlQuery q(db);
    bool res = q.exec("CREATE TABLE IF NOT EXISTS scanner (id INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT, freq VARCHAR(255) NOT NULL DEFAULT '01000000000101', lo ENUM(0,1) NOT NULL DEFAULT 0, hang ENUM(0,1) NOT NULL DEFAULT 0)" );
    if(!res) {
        qDebug() << "ERROR: Creating table 'scanner' returned a SQL error: " << q.lastError().text();
    } else {
        qDebug() << "WARN: SimplePCR1000 Scanner Table created/or it already exists!";
    }

    //Check that there is a settings table in the DB
    /*
    q.prepare("CREATE TABLE IF NOT EXISTS settings (id INTEGER UNIQUE PRIMARY KEY, name VARCHAR(25) NOT NULL DEFAULT 'NONAME', value VARCHAR(12) NOT NULL DEFAULT '0')" );
    if(!q.exec()) {
        qDebug() << "ERROR: " << q.lastError();
    } else {
        qDebug() << "WARN: SimplePCR1000 Settings Table created/or it already exists!";
        qDebug() << "LOG: Settings Table Query 1";
        q.prepare("INSERT OR IGNORE INTO settings (id,name,value) VALUES(1,'IsFirstRun','1')");
        if(!q.exec()) { qDebug() << "ERROR: DB Connect SQL Error: " << q.lastError().text(); return false; }
        qDebug() << "LOG: Settings Table Query 2";
        q.prepare("INSERT OR IGNORE INTO settings (id,name,value) VALUES(2,'SerialPort','/dev/ttyUSB0')");
        if(!q.exec()) { qDebug() << "ERROR: DB Connect SQL Error: " << q.lastError().text(); return false; }
        qDebug() << "LOG: Settings Table Query 3";
        q.prepare("INSERT OR IGNORE  INTO settings (id,name,value) VALUES(3,'CurFreq','100')");
        if(!q.exec()) { qDebug() << "ERROR: DB Connect SQL Error: " << q.lastError().text(); return false; }
        qDebug() << "LOG: Settings Table Query 4";
        q.prepare("INSERT OR IGNORE  INTO settings (id,name,value) VALUES(4,'CurVolume','50')");
        if(!q.exec()) { qDebug() << "ERROR: DB Connect SQL Error: " << q.lastError().text(); return false; }
        qDebug() << "LOG: Settings Table Query 5";
        q.prepare("INSERT OR IGNORE  INTO settings (id,name,value) VALUES(5,'CurSquelch','50')");
        if(!q.exec()) { qDebug() << "ERROR: DB Connect SQL Error: " << q.lastError().text(); return false; }
        qDebug() << "LOG: Settings Table Query 6";
        q.prepare("INSERT OR IGNORE  INTO settings (id,name,value) VALUES(6,'CurMode','AM')");
        if(!q.exec()) { qDebug() << "ERROR: DB Connect SQL Error: " << q.lastError().text(); return false; }
        qDebug() << "LOG: Settings Table Query 7";
        q.prepare("INSERT OR IGNORE  INTO settings (id,name,value) VALUES(7,'CurFilter','3KHz')");
        if(!q.exec()) { qDebug() << "ERROR: DB Connect SQL Error: " << q.lastError().text(); return false; }
        qDebug() << "LOG: Settings Table Query 8";
        q.prepare("INSERT OR IGNORE  INTO settings (id,name,value) VALUES(8,'CurIFShift','127')");
        if(!q.exec()) { qDebug() << "ERROR: DB Connect SQL Error: " << q.lastError().text(); return false; }
        qDebug() << "LOG: Settings Table Query 9";
        q.prepare("INSERT OR IGNORE  INTO settings (id,name,value) VALUES(9,'AutoStartReminder','1')");
        if(!q.exec()) { qDebug() << "ERROR: DB Connect SQL Error: " << q.lastError().text(); return false; }
        qDebug() << "LOG: Settings Table Query 10";
        q.prepare("INSERT OR IGNORE  INTO settings (id,name,value) VALUES(10,'IsFirst15Runs','0')");
        if(!q.exec()) { qDebug() << "ERROR: DB Connect SQL Error: " << q.lastError().text(); return false; }
        qDebug() << "LOG: Settings Table Query 11";
        q.prepare("INSERT OR IGNORE  INTO settings (id,name,value) VALUES(11,'FitToDesktop','0')");
        if(!q.exec()) { qDebug() << "ERROR: DB Connect SQL Error: " << q.lastError().text(); return false; }
        qDebug() << "LOG: Settings Table Query 12";
        q.prepare("INSERT OR IGNORE  INTO settings (id,name,value) VALUES(12,'AutoStart','0')");
        if(!q.exec()) { qDebug() << "ERROR: DB Connect SQL Error: " << q.lastError().text(); return false; }
        qDebug() << "LOG: SimplePCR1000 Settings Table is properly configured!";
    }
    */
    return db.open();
}

bool Database::Disconnect() {
    if(db.isOpen()) {
        db.close();
        return true;
    }
    return false;
}

/********************************************************************************/
/*** Start Scanner table fucntions ************************************************/
/********************************************************************************/

QStringList Database::GetAllChannels()
{
    QSqlQuery q(db);
    QStringList freqs;
    bool res = q.exec("SELECT * FROM scanner");
    if(!res) { qDebug() << "ERROR: GetAllScanners SQL Error: " << q.lastError().text(); return freqs; }
    QSqlRecord r;
    r = q.record();
    while(q.next()) {
        freqs << q.value(r.indexOf("freq")).toString();
    }
    qDebug() << "LOG: GetAllScanners queried successfully!";
    return freqs;
}

bool Database::RemoveAllChannels()
{
    QSqlQuery q(db);
    q.prepare("DROP TABLE scanner");
    q.exec();
    q.prepare("CREATE TABLE IF NOT EXISTS scanner (id INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT, freq VARCHAR(255) NOT NULL DEFAULT '01000000000101', lo ENUM(0,1) NOT NULL DEFAULT 0, hang ENUM(0,1) NOT NULL DEFAULT 0)" );
    if(!q.exec()) { qDebug() << "ERROR: Creating table 'scanner' returned a SQL error: " << q.lastError().text(); return false; }
    qDebug() << "LOG: New scanners table created in database!";
    qDebug() << "LOG: All channels removed";
    return true;
}

bool Database::RemoveChannels(const QStringList& freqs)
{
    QSqlQuery q;
    for(int i = 0; i < freqs.count(); ++i) {
        q.prepare("DELETE FROM scanner WHERE freq='" + freqs[i] + "'");
        if(!q.exec()) { qDebug() << "ERROR: RemoveChannels SQL Error: " << q.lastError().text(); return false; }
    }
    qDebug() << "LOG: RemoveChannels queried successfully!";
    return true;
}

bool Database::AddChannel(QString freq)
{
    QSqlQuery q;
    for(int i = 0; i < freq.count(); ++i)
    {
        q.prepare("INSERT OR IGNORE INTO scanner (freq) VALUES('" + freq[i] + "')");
        if(!q.exec()) { qDebug() << "ERROR: AddChannels SQL Error: " << q.lastError().text(); return false; }
    }
    qDebug() << "LOG: AddChannels queried successfully!";
    return true;
}

QString Database::GetMode(QString freq)
{
    QSqlQuery q;
    q.prepare("SELECT mode FROM scanner WHERE freq='" + freq + "'");
    if(!q.exec()) { qDebug() << "ERROR: GetMode SQL Error: " << q.lastError().text(); return false; }
    qDebug() << "LOG: GetMode queried successfully!";
    return COM.ModeComboBox.at(q.value(0).toInt());
}

QString Database::GetFilter(QString freq)
{
    QSqlQuery q;
    q.prepare("SELECT filter FROM scanner WHERE freq='" + freq + "'");
    if(!q.exec()) { qDebug() << "ERROR: GetFilter SQL Error: " << q.lastError().text(); return false; }
    qDebug() << "LOG: GetFilter queried successfully!";
    return COM.FilterComboBox.at(q.value(0).toInt());
}

bool Database::GetHang(QString freq)
{
    QSqlQuery q;
    q.prepare("SELECT hang FROM scanner WHERE freq='" + freq + "'");
    if(!q.exec()) { qDebug() << "ERROR: GetHang SQL Error: " << q.lastError().text(); return false; }
    qDebug() << "LOG: GetHang queried successfully!";
    return COM.n2b(q.value(0).toInt());
}

bool Database::GetLockout(QString freq)
{
    QSqlQuery q;
    q.prepare("SELECT lo FROM scanner WHERE freq='" + freq + "'");
    if(!q.exec()) { qDebug() << "ERROR: GetLockout SQL Error: " << q.lastError().text(); return false; }
    qDebug() << "LOG: GetLockout queried successfully!";
    return COM.n2b(q.value(0).toInt());
}

bool Database::EditChannel(QString old_freq, QString new_freq)
{
    QSqlQuery q;
    q.prepare("UPDATE OR IGNORE scanner SET freq='" + new_freq + "' WHERE freq='" + old_freq + "'");
    if(!q.exec()) { qDebug() << "ERROR: EditChannel SQL Error: " << q.lastError().text(); return false; }
    qDebug() << "LOG: EditChannel queried successfully!";
    return true;
}

void Database::LockoutChannel(QString freq, bool lo)
{
    QSqlQuery q;
    q.prepare("UPDATE OR IGNORE scanner SET lo='" + QString::number(COM.b2n(lo)) + "' WHERE freq='" + freq + "'");
    if(!q.exec()) { qDebug() << "ERROR: LockoutChannel SQL Error: " << q.lastError().text(); }
    qDebug() << "LOG: LockoutChannel queried successfully!";
}

void Database::HangChannel(QString freq, bool hang)
{
    QSqlQuery q;
    q.prepare("UPDATE OR IGNORE scanner SET hang='" + QString::number(COM.b2n(hang)) + "' WHERE freq='" + freq + "'");
    if(!q.exec()) { qDebug() << "ERROR: HangChannel SQL Error: " << q.lastError().text(); }
    qDebug() << "LOG: HangChannel queried successfully!";
}

bool Database::CheckChannelExists(QString freq)
{
    QSqlQuery q;
    q.prepare("SELECT COUNT(*) AS 'records' FROM scanner WHERE freq='" + freq + "'");
    if(!q.exec()) { qDebug() << "ERROR: CheckChannelExists SQL Error: " << q.lastError().text(); return false; }
    qDebug() << "LOG: CheckChannelExists queried successfully! - Returned a value of: '" << QString::number(COM.n2b(q.value(0).toInt())) << "'";
    if(q.value(0).toInt() > 0) { return true; } else { return false; }
}

int Database::ChannelCount()
{
    QSqlQuery q;
    q.prepare("SELECT COUNT(*) AS 'records' FROM scanner");
    if(!q.exec()) { qDebug() << "ERROR: ChannelCount SQL Error: " << q.lastError().text(); return 0; }
    qDebug() << "LOG: ChannelCount queried successfully! - Returned a value of: '" << QString::number(COM.n2b(q.value(0).toInt())) << "'";
    return COM.n2b(q.value(0).toInt());
    ///return GetAllChannels().count();
}

/********************************************************************************/
/*** Start Settings table fucntions *********************************************/
/********************************************************************************/


QVariant Database::getConf(QString name, bool DoNotFormat) { //if DoNotFormat = TRUE, the data will not be touched going in or out of the DB
    QSqlQuery q;
    QSqlRecord r;
    QString qstr = "SELECT value FROM settings WHERE name='" + name + "'";
    q.prepare(qstr);
    if(!q.exec()) { qDebug() << "ERROR: getConf(name=" << name << ") SQL Error: " << q.lastError().text(); return false; }
    r = q.record();
    q.first();
    QVariant val = q.value(r.indexOf("value"));
    qDebug() << "LOG: Config value '" << name << "' ** WAS ** found in the database!";
    if(DoNotFormat) { return val; } else { return formatFromDB(name, val); }
}

bool Database::setConf(QString name, QVariant value, bool DoNotFormat) { //if DoNotFormat = TRUE, the data will not be touched going in or out of the DB
    QSqlQuery q;
    QVariant dbvalue;
    if(DoNotFormat) { dbvalue = value; } else { dbvalue = formatToDB(name, value); }
    q.prepare("UPDATE settings SET value='"+dbvalue.toString()+"' WHERE name='" + name + "'");
    if(!q.exec()) { qDebug() << "ERROR: setConf(name=" << name << ", value=" << value.toString() << ") SQL Error: " << q.lastError().text(); return false; }
    qDebug() << "LOG: Config value '" << name << "' ** WAS ** successfully applied to the database!";
    return true;
}

//Make the DB values readable by the application, and vice-versa

QVariant Database::formatFromDB(QString name, QVariant val)
{
    bool ok = false;
    int i;
    QString s;
    if(name==S_FREQ || name==S_PORT || name==S_MODE || name==S_FILTER || name==S_IFSHIFT || name == S_VOL || name==S_SQU) {
        s = val.toString();
        return s;
    } else if(name==S_F15RUNS) {
        i = val.toInt(&ok);
        if(!ok) {return -1; } else { return i; }
    } else if(name==S_FIRSTRUN) {
        return COM.n2b(val.toInt());
    } else {
        qDebug() << "WARN: Config name ('" << name << "') is an unknown or outdated configuration name!";
        return -1;
    }
    return 0;
}

QVariant Database::formatToDB(QString name, QVariant val)
{
    bool ok = false;
    int i;
    QString s;
    if(name==S_FREQ || name==S_PORT || name==S_MODE || name==S_FILTER || name==S_IFSHIFT || name == S_VOL || name==S_SQU) {
        s = val.toString();
        return s;
    } else if(name==S_F15RUNS) {
        i = val.toInt(&ok);
        if(!ok) { return -1; } else { return i; }
    } else if(name==S_FIRSTRUN) {
        return COM.b2n(val.toBool());
    } else {
        qDebug() << "WARN: Config name ('" << name << "') is an unknown or outdated configuration name!";
        return -1;
    }
    return 0;
}

// END Database Class ***************************************************************/
/************************************************************************************/
